package com.androidhive.androidlistview;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "contactsManager";


	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + "contacts");

		// Create tables again
		onCreate(db);
	}

	/**
	 * All CRUD(Create, Read, Update, Delete) Operations
	 */

	// Adding new contact
	void addContact(String name,String nu) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put("name", name); // Contact Name
		values.put("phone_number",nu); // Contact Phone

		// Inserting Row
		db.insert("contacts", null, values);
		db.close(); // Closing database connection
	}
	
	void addVendor(int Id, String companyName, String companyDetails, String contactnu, String emailId, String companyadd) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();//(`Id`, `companyName`, `companyDetails`, `contactnu`, `emailId`, `companyadd`)
		values.put("Id", Id); // Contact Name
		values.put("companyName",companyName); // Contact Phone
		values.put("companyDetails",companyDetails ); 
		values.put("contactnu", contactnu); 
		values.put("emailId",emailId ); 
		values.put("companyadd",companyadd ); 
		// Inserting Row
		db.insert("vendor", null, values);
		db.close(); // Closing database connection
	}
	//`vendorlogin` (`Id`, `userName`, `password`) 
	void addVendorlogin(int Id, String userName, String password) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();//(`Id`, `companyName`, `companyDetails`, `contactnu`, `emailId`, `companyadd`)
		values.put("Id", Id); // Contact Name
		values.put("userName",userName); // Contact Phone
		values.put("password",password ); 
		
		// Inserting Row
		db.insert("vendorlogin", null, values);
		db.close(); // Closing database connection
	}

	void deleteCrop(int Id){
		
		String countQuery = "DELETE  FROM " + "masterbidtable where cropId="+Id+";";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		countQuery = "DELETE  FROM " + "mastercroptable where Id="+Id+";";
		
		 cursor = db.rawQuery(countQuery, null);
		
	
		db.close();
		Log.d("In","Delete Crop" );
	}
	void addFarmer(int Id,String Name, String Contactnu,String emailId, String firmLocation, String firmState) {
		SQLiteDatabase db = this.getWritableDatabase();
//CREATE TABLE IF NOT EXISTS `farmer`
		//(`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`Name` varchar(50) NOT NULL,`Contactnu` varchar(10) NOT NULL,`emailId` varchar(50) NOT NULL,`firmLocation` varchar(50) NOT NULL, `firmState` varchar(50) NOT NULL );";
		ContentValues values = new ContentValues();//(`Id`, `companyName`, `companyDetails`, `contactnu`, `emailId`, `companyadd`)
		values.put("Id", Id); // Contact Name
		values.put("Name",Name); // Contact Phone
		values.put("Contactnu",Contactnu ); 
		values.put("emailId", emailId); 
		values.put("firmLocation",firmLocation); 
		values.put("firmState",firmState); 
	
		// Inserting Row
		db.insert("farmer", null, values);
		db.close(); // Closing database connection
	}
	 
	void addFarmerlogin(int Id, String userName, String password) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();//(`Id`, `companyName`, `companyDetails`, `contactnu`, `emailId`, `companyadd`)
		values.put("Id", Id); // Contact Name
		values.put("userName",userName); // Contact Phone
		values.put("password",password ); 
		
		// Inserting Row
		db.insert("farmerlogin", null, values);
		db.close(); // Closing database connection
	}


	
	void addToMastercroptable(int Id,String cropname, int farmerId, String cropQuality,int cost, int baseCost,int pkgCost, String fname,String state, int quantity) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();//(`Id`, `companyName`, `companyDetails`, `contactnu`, `emailId`, `companyadd`)
		values.put("Id", Id); // Contact Name
		values.put("cropname",cropname); // Contact Phone
		values.put("farmerId",farmerId ); 
		values.put("cropQuality", cropQuality); 
		values.put("cost",cost); 
		values.put("baseCost",baseCost ); 
		values.put("pkgCost",pkgCost ); 
		values.put("fname", fname); 
		values.put("state", state); 
		values.put("quantity",quantity); 
	
		// Inserting Row
		db.insert("mastercroptable", null, values);
		db.close(); // Closing database connection
	}
	void addToMastercroptableByFarmer(String cropname, int farmerId, String cropQuality,int cost, int baseCost,int pkgCost, String fname,String state, int quantity) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();//(`Id`, `companyName`, `companyDetails`, `contactnu`, `emailId`, `companyadd`)
	//	values.put("Id", Id); // Contact Name
		values.put("cropname",cropname); // Contact Phone
		values.put("farmerId",farmerId ); 
		values.put("cropQuality", cropQuality); 
		values.put("cost",cost); 
		values.put("baseCost",baseCost ); 
		values.put("pkgCost",pkgCost ); 
		values.put("fname", fname); 
		values.put("state", state); 
		values.put("quantity",quantity); 
	
		// Inserting Row
		db.insert("mastercroptable", null, values);
		db.close(); // Closing database connection
	}
	
	void updateMastercroptable(int Id,int cost) {
		SQLiteDatabase db = this.getWritableDatabase();
       
		String selectQuery = "SELECT cost from mastercroptable where Id="+Id+";";
		Cursor cursor = db.rawQuery(selectQuery, null);
		if (cursor.moveToFirst()) {
			do {
				if(Integer.parseInt(cursor.getString(0))<cost){
					
					
					String qry="update mastercroptable set cost="+cost+" where Id="+Id+";";
					db.execSQL(qry);
				}
				Log.d("old cost",cursor.getString(0));
				//return (Integer.parseInt(cursor.getString(0)));
				
			} while (cursor.moveToNext());
		}
		cursor.close();
	
	
		// Inserting Row
		//db.insert("mastercroptable", null, values);
		db.close(); // Closing database connection
	}
	
	void updateFarmerProfile(int Id,String fname,String fcnu,String feid,String floc,String fstate) {
		SQLiteDatabase db = this.getWritableDatabase();
       
		String selectQuery = "SELECT cost from mastercroptable where Id="+Id+";";
		Cursor cursor = db.rawQuery(selectQuery, null);
		if (cursor.moveToFirst()) {
			do {
				
					
					//farmer`(`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`Name` varchar(50) NOT NULL,`Contactnu` varchar(10) NOT NULL,`emailId` varchar(50) NOT NULL,`firmLocation` varchar(50) NOT NULL, `firmState`
					String qry="update farmer set Name='"+fname+"' ,Contactnu='"+fcnu+"' ,emailId='"+feid+"' ,firmLocation='"+floc+"' ,firmState='"+fstate+"' where Id="+Id+";";
					db.execSQL(qry);
				
			
				
				
			} while (cursor.moveToNext());
		}
		cursor.close();
	
	
		// Inserting Row
		//db.insert("mastercroptable", null, values);
		db.close(); // Closing database connection
	}
	
	
	
	void updateVendorProfile(int Id,String companyName,String companyDetails,String contactnu,String emailId,String companyadd) {
		SQLiteDatabase db = this.getWritableDatabase();
       
		String selectQuery = "SELECT cost from mastercroptable where Id="+Id+";";
		Cursor cursor = db.rawQuery(selectQuery, null);
		if (cursor.moveToFirst()) {
			do {
				
					
//vendor` (`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`companyName` varchar(50) NOT NULL,`companyDetails` varchar(200) NOT NULL,`contactnu` varchar(10) NOT NULL,`emailId` varchar(50) NOT NULL,`companyadd` varchar(200) NOT NULL);";					
					String qry="update vendor set companyName='"+companyName+"' ,companyDetails='"+companyDetails+"' ,contactnu='"+contactnu+"' ,emailId='"+emailId+"' ,companyadd='"+companyadd+"' where Id="+Id+";";
					db.execSQL(qry);
				
			
				
				
			} while (cursor.moveToNext());
		}
		cursor.close();
	
	
		// Inserting Row
		//db.insert("mastercroptable", null, values);
		db.close(); // Closing database connection
	}
	
	public String getCrops(String colm,String patn,int cnt) {
	
		// Select All Query
		String selectQuery = "SELECT "+colm+ " FROM mastercroptable where cropname like '%"+patn+"%'";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		String contact = new String();
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
			
//				contact.setID(Integer.parseInt(cursor.getString(0)));
//				contact.setName(cursor.getString(1));
//				contact.setPhoneNumber(cursor.getString(2));
				// Adding contact to list
				for(int j=0;j<cnt;j++){
					//contact=contact+cursor.getString(0)+"#"+cursor.getString(1);
						contact=contact+cursor.getString(j)+"#";
						
					}
				contact=contact+"</br>";
//				contactList.add(contact);
			} while (cursor.moveToNext());
		}
		cursor.close();
		// return contact list
		Log.d("In getCrops:",contact);
		
		
		db.close();
		return contact;
	}
	public String getCropsByState(String colm,String patn,int cnt) {
		Log.d("Inside","DB");
		// Select All Query
		String selectQuery = "SELECT "+colm+ " FROM mastercroptable where state='"+patn+"';";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		String contact = new String();
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
			

				for(int j=0;j<cnt;j++){
				
						contact=contact+cursor.getString(j)+"#";
						
					}
				contact=contact+"</br>";

			} while (cursor.moveToNext());
		}
		cursor.close();
	
		Log.d("In getCropsByState",contact);
		
		
		db.close();
		return contact;
	}
	public String getAllCrops(String colm,int cnt) {
		Log.d("Inside","DB");
		// Select All Query
		String selectQuery = "SELECT "+colm+ " FROM mastercroptable;";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		String contact = new String();
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
			

				for(int j=0;j<cnt;j++){
				
						contact=contact+cursor.getString(j)+"#";
						
					}
				contact=contact+"</br>";

			} while (cursor.moveToNext());
		}
		cursor.close();
	
		Log.d("In getCropsByState",contact);
		
		
		db.close();
		return contact;
	}
	
	public String getBidOnCrops(int CropId) {
		
		// Select All Query`masterbidtable` (`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`cropId` int(10) NOT NULL,`vendorId` int(10) NOT NULL,`bidCost` int(10) NOT NULL,`quantity` int(10)
		String selectQuery = "SELECT vendorId,pkgCost,quantity,bidCost from masterbidtable WHERE cropId = "+CropId+";" ;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		String contact = new String();
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
			
//				contact.setID(Integer.parseInt(cursor.getString(0)));
//				contact.setName(cursor.getString(1));
//				contact.setPhoneNumber(cursor.getString(2));
				// Adding contact to list
				String vName=new String();
				for(int j=0;j<4;j++){
					//contact=contact+cursor.getString(0)+"#"+cursor.getString(1);
						contact=contact+cursor.getString(j)+"#";
						
					}
				vName=null;
				String subQry="Select companyName from vendor WHERE Id = "+Integer.parseInt(cursor.getString(0))+";";
				Cursor cursor1 = db.rawQuery(subQry, null);
				if (cursor1.moveToFirst()) {
					do {
					vName=cursor1.getString(0);
					}while (cursor1.moveToNext());
				}
				contact=contact+vName+"#";
				contact=contact+"</br>";
//				contactList.add(contact);
			} while (cursor.moveToNext());
		}
		cursor.close();
		// return contact list
		Log.d("FarmerCropBid:",contact);
		
		
		db.close();
		return contact;
	}
	
	
	
	
	public String FarmersCrops(int farmerId) {
		
		// Select All Query`mastercroptable` (`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`cropname` varchar(50) NOT NULL,`farmerId` int(50) NOT 
		//NULL,`cropQuality` varchar(50) NOT NULL, `cost` int(10) NOT NULL, `baseCost` int(10) NOT NULL,`fname` varchar(50) NOT NULL, `quantity` int(10) NOT NULL
		String selectQuery = "SELECT Id,cropname,cropQuality,quantity FROM mastercroptable where farmerId = "+farmerId+";";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		String contact = new String();
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
			
//				contact.setID(Integer.parseInt(cursor.getString(0)));
//				contact.setName(cursor.getString(1));
//				contact.setPhoneNumber(cursor.getString(2));
				// Adding contact to list
				for(int j=0;j<4;j++){
					//contact=contact+cursor.getString(0)+"#"+cursor.getString(1);
						contact=contact+cursor.getString(j)+"#";
						
					}
				contact=contact+"</br>";
//				contactList.add(contact);
			} while (cursor.moveToNext());
		}
		cursor.close();
		// return contact list
		Log.d("croptablePrabhu:",contact);
		
		
		db.close();
		return contact;
	}
	
	public int getCropCount() {
		int i=0;
		String countQuery = "SELECT  * FROM " + "mastercroptable";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		if (cursor.moveToFirst()) {
			do {
				i++;
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		// return count
		return i;
	}
	public int getbaseCost(int cropId) {
		int i=0;
		String countQuery = "SELECT  baseCost FROM " + "mastercroptable where Id="+cropId+";";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		if (cursor.moveToFirst()) {
			do {
				Log.d("cost",cursor.getString(0));
				return (Integer.parseInt(cursor.getString(0)));
				
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		// return count
		return i;
	}
	public String getpkgCost(int cropId) {
		String i=new String(" ");
		String countQuery = "SELECT  pkgCost FROM " + "mastercroptable where Id="+cropId+";";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		if (cursor.moveToFirst()) {
			do {
				Log.d("cost",cursor.getString(0));
				return (cursor.getString(0));
				
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		// return count
		return i;
	}
	
	public int getQuantity(int cropId) {
		int i=0;
		String countQuery = "SELECT  quantity FROM " + "mastercroptable where Id="+cropId+";";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		if (cursor.moveToFirst()) {
			do {
				Log.d("quantity",cursor.getString(0));
				return (Integer.parseInt(cursor.getString(0)));
				
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		// return count
		return i;
	}
	
	
	//`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`cropId` int(10) NOT NULL,`vendorId` int(10) NOT NULL,`bidCost` int(10) NOT NULL,`quantity` int(10)
	void addToMasterBidtable(int  cropId, int vendorId, int bidCost,String pkgcost,int quantity) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();//(
		//values.put("Id", Id); 
		values.put("cropId",cropId);
		values.put("vendorId",vendorId ); 
		values.put("bidCost", bidCost); 
		values.put("quantity",quantity ); 
		values.put("pkgcost",pkgcost ); 
	
	
		// Inserting Row
		db.insert("masterbidtable", null, values);
		
		String selectQuery = "SELECT  * FROM " + "masterbidtable;";

	//	SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do
		{Log.d(cursor.getString(0),cursor.getString(1)+cursor.getString(2)+cursor.getString(3)+cursor.getString(4));
				
				
			} 	while (cursor.moveToNext());
		}

		// return contact list
	
		
		db.close(); // Closing database connection
		
	}
	
	
	// Getting single contact
	Contact getContact(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query("contacts", new String[] { "id",
				"name", "phone_number" }, "id" + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
				cursor.getString(1), cursor.getString(2));
		// return contact
		db.close();
		return contact;
	}
	
	// Getting All Contacts
	public List<Contact> getAllContacts() {
		List<Contact> contactList = new ArrayList<Contact>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + "contacts";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Contact contact = new Contact();
				contact.setID(Integer.parseInt(cursor.getString(0)));
				contact.setName(cursor.getString(1));
				contact.setPhoneNumber(cursor.getString(2));
				// Adding contact to list
				contactList.add(contact);
			} while (cursor.moveToNext());
		}

		// return contact list
		db.close();
		return contactList;
	}

	// Updating single contact
	public int updateContact(Contact contact) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put("name", contact.getName());
		values.put("phone_number", contact.getPhoneNumber());

		// updating row
		return db.update("contacts", values, "id" + " = ?",
				new String[] { String.valueOf(contact.getID()) });
	}

	// Deleting single contact
	public void deleteContact(Contact contact) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete("contacts", "id" + " = ?",
				new String[] { String.valueOf(contact.getID()) });
		db.close();
	}


	// Getting contacts Count
	public int getContactsCount() {
		int i=0;
		String countQuery = "SELECT  * FROM " + "contacts";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		if (cursor.moveToFirst()) {
			do {
				i++;
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		// return count
		return i;
	}
	public String getFname(int Id){
		String Query = "SELECT  Name FROM " + "farmer where Id="+Id+";";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(Query, null);
		if (cursor.moveToFirst()) {
			do {
				Log.d("fname",cursor.getString(0));
				return (cursor.getString(0));
				
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		
		return "0";
	}
	
	public String getFarmerProfile(int Id){
		String FProfile=new String();
	Log.d("DB:","1");
		String Query ="SELECT Name, Contactnu, emailId, firmLocation, firmState from farmer where Id="+Id+";";
		SQLiteDatabase db = this.getReadableDatabase();
		Log.d("DB:","2");
		//`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`Name` varchar(50) NOT NULL,`Contactnu` varchar(10) NOT NULL,`,emailId` varchar(50) NOT NULL,`firmLocation` varchar(50) NOT NULL, `firmState`
		Cursor cursor = db.rawQuery(Query, null);
		if (cursor.moveToFirst()) {
			do {Log.d("DB:","3");
				FProfile=FProfile+cursor.getString(0)+"#"+cursor.getString(1)+"#"+cursor.getString(2)
				+"#"+cursor.getString(3)+"#"+cursor.getString(4)+"#";
				Log.d("FarmerProfile:",FProfile);
				Log.d("DB:","4");
				
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		
		
	Log.d("FProfile",FProfile);	
	return FProfile;
	
	}
	public int getFarmerId(int cropId){
		String FProfile=new String();
	Log.d("DB:","1");
		String Query ="SELECT farmerId from mastercroptable where Id="+cropId+";";
		SQLiteDatabase db = this.getReadableDatabase();
		Log.d("DB:","2");
		//`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`Name` varchar(50) NOT NULL,`Contactnu` varchar(10) NOT NULL,`,emailId` varchar(50) NOT NULL,`firmLocation` varchar(50) NOT NULL, `firmState`
		Cursor cursor = db.rawQuery(Query, null);
		if (cursor.moveToFirst()) {
			do {Log.d("DB:","3");
				return(cursor.getInt(0));
				
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		
		
	
	return -1;
	
	}
	public String getVendorProfile(int Id){
		String VProfile=new String();
	//vendor` (`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`companyName` varchar(50) NOT NULL,`companyDetails` varchar(200) NOT NULL,`contactnu` varchar(10) NOT NULL,`emailId` varchar(50) NOT NULL,`companyadd` 
		String Query ="SELECT companyName, companyDetails, contactnu, emailId, companyadd  from vendor where Id="+Id+";";
		SQLiteDatabase db = this.getReadableDatabase();
		Log.d("DB:","2");
		//`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`Name` varchar(50) NOT NULL,`Contactnu` varchar(10) NOT NULL,`,emailId` varchar(50) NOT NULL,`firmLocation` varchar(50) NOT NULL, `firmState`
		Cursor cursor = db.rawQuery(Query, null);
		if (cursor.moveToFirst()) {
			do {
				VProfile=VProfile+cursor.getString(0)+"#"+cursor.getString(1)+"#"+cursor.getString(2)
				+"#"+cursor.getString(3)+"#"+cursor.getString(4)+"#";
				Log.d("VendorProfile:",VProfile);
				Log.d("DB:","4");
				
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		
		
	Log.d("VProfile",VProfile);	
	return VProfile;
	
	}
	
	public void onCreate(SQLiteDatabase db) {
		String CREATE_CONTACTS_TABLE = "CREATE TABLE " + "contacts" + "("
				+ "id" + " INTEGER PRIMARY KEY," + "name" + " TEXT,"
				+ "phone_number" + " TEXT" + ")";
		db.execSQL(CREATE_CONTACTS_TABLE);
		
		
		Log.d("oncreate ", "success1");
		
		
		String qry=new String();
		qry="PRAGMA foreign_keys = ON;";
		db.execSQL(qry);
		
		
		//-- Dumping structure for table farmervendorcollaberation.farmer
		qry="CREATE TABLE IF NOT EXISTS `farmer`(`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`Name` varchar(50) NOT NULL,`Contactnu` varchar(10) NOT NULL,`emailId` varchar(50) NOT NULL,`firmLocation` varchar(50) NOT NULL, `firmState` varchar(50) NOT NULL );";
		db.execSQL(qry);
	
		
		qry="CREATE TABLE IF NOT EXISTS `farmerlogin` (`Id` int(10)  PRIMARY KEY ,`userName` varchar(50) NOT NULL,`password` varchar(50) NOT NULL,CONSTRAINT `FK_farmerlogin_farmer` FOREIGN KEY (`Id`) REFERENCES `farmer` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE);";
		db.execSQL(qry);
		
		qry="CREATE TABLE IF NOT EXISTS `vendor` (`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`companyName` varchar(50) NOT NULL,`companyDetails` varchar(200) NOT NULL,`contactnu` varchar(10) NOT NULL,`emailId` varchar(50) NOT NULL,`companyadd` varchar(200) NOT NULL);";
		db.execSQL(qry);
		
		qry="CREATE TABLE IF NOT EXISTS `vendorlogin` (`Id` int(10) PRIMARY KEY,`userName` varchar(50) NOT NULL,`password` varchar(50) NOT NULL,CONSTRAINT `FK_vendorlogin_vendor` FOREIGN KEY (`Id`) REFERENCES `vendor` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE);";
		db.execSQL(qry);
		
		qry="CREATE TABLE IF NOT EXISTS `mastercroptable` (`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`cropname` varchar(50) NOT NULL,`farmerId` int(50) NOT NULL,`cropQuality` varchar(50) NOT NULL, `cost` int(10) NOT NULL, `baseCost` int(10) NOT NULL,'pkgCost' int(10) NOT NULL,`fname` varchar(50) NOT NULL,`state` varchar(100) NOT NULL, `quantity` int(10) NOT NULL,CONSTRAINT `FK_mastercroptable_farmer` FOREIGN KEY (`farmerId`) REFERENCES `farmer` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE);";
		db.execSQL(qry);
		
		qry="CREATE TABLE IF NOT EXISTS `masterbidtable` (`Id` INTEGER PRIMARY KEY AUTOINCREMENT,`cropId` int(10) NOT NULL,`vendorId` int(10) NOT NULL,`bidCost` int(10) NOT NULL,`pkgCost` varchar(50) NOT NULL,`quantity` int(10) NOT NULL,CONSTRAINT `FK_masterbidtable_mastercroptable` FOREIGN KEY (`cropId`) REFERENCES `mastercroptable` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_masterbidtable_vendor` FOREIGN KEY (`vendorId`) REFERENCES `vendor` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE);";
		db.execSQL(qry);
		Log.d("oncreate ", "success2");
	//	db.close();	
//	
		
		
		
	
			
//				qry="insert INTO `vendor` (`Id`, `companyName`, `companyDetails`, `contactnu`, `emailId`, `companyadd`) VALUES(1, 'Dalmiya Limited', 'rice mill', '123456789', 'asdf@gmail.com', 'Nagpur');"+
//				"insert INTO `vendor` VALUES (2, 'Agrotech pvt ltd', 'oil refinary', '432156789', 'dfghjkl@yahoo.com', 'Mumbai');";
//				
//				db.execSQL(qry);
//				Log.d("oncreate ", "success6");
				
				
				//-- Dumping structure for table farmervendorcollaberation.vendorlogin
				
				
//				qry="insert INTO `vendorlogin` (`Id`, `userName`, `password`) VALUES (1, 'dalmiya', 'dalmiya');"+
//				"insert INTO `vendorlogin` VALUES (2, 'agrotech', 'agrotech');";
//				db.execSQL(qry);
//		
//		
//				//-- Dumping structure for table farmervendorcollaberation.mastercroptable
//			

//		
//				Log.d("oncreate ", "success7");
		//-- Dumping structure for table farmervendorcollaberation.masterbidtable
		
		//-- Dumping data for table farmervendorcollaberation.masterbidtable: ~0 rows (approximately)
		/*!40000 ALTER TABLE `masterbidtable` DISABLE KEYS */;
		/*!40000 ALTER TABLE `masterbidtable` ENABLE KEYS */;


		//addContact(new Contact("Ravi", "9100000000"));
	}
	public int getVendorId(String userId,String Password) {
		//List<> contactList = new ArrayList<Contact>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + "vendorlogin;";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do
		{Log.d(cursor.getString(1),cursor.getString(2)+cursor.getString(0));
				if(cursor.getString(1).equals(userId.trim())&&cursor.getString(2).equals(Password.trim()))
				return (Integer.parseInt(cursor.getString(0)));
				
			} 	while (cursor.moveToNext());
		}

		// return contact list
		db.close();
		return 0;
	}
	
	public int getFarmerId(String userId,String Password) {
		//List<> contactList = new ArrayList<Contact>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + "farmerlogin;";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do
		{Log.d(cursor.getString(1),cursor.getString(2)+cursor.getString(0));
				if(cursor.getString(1).equals(userId.trim())&&cursor.getString(2).equals(Password.trim()))
				{Log.d("hk",cursor.getString(0));
				return (Integer.parseInt(cursor.getString(0)));
				}
			} 	while (cursor.moveToNext());
		}

		// return contact list
		db.close();
		return 0;
	}
	public String sendSmsToFarmer(int  cropId, int vendorId, int bidCost,int quantity){
		int fId=0;
		String fname=new String();
		String vendor= new String();
		String cropname=new String();
		String cropQuality=new String();
		String phonenu=new String();
		String msg=new String();
		String selectQuery = "SELECT  farmerId,fname,cropname,cropQuality FROM " + "mastercroptable WHERE Id="+cropId+";";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		if (cursor.moveToFirst()) {
			do
		{			
				fId=Integer.parseInt(cursor.getString(0));
				fname=cursor.getString(1);
				cropname=cursor.getString(2);
				cropQuality=cursor.getString(3);
				Log.d(cursor.getString(0),cursor.getString(1)+cursor.getString(2)+cursor.getString(3));
				
			} 	while (cursor.moveToNext());
		}
		String selectQuery1 = "SELECT  Contactnu FROM farmer WHERE Id="+fId+";";

	
		Cursor cursor1 = db.rawQuery(selectQuery1, null);
		if (cursor1.moveToFirst()) {
			do
		{		phonenu=cursor1.getString(0);
				Log.d("nu",cursor1.getString(0));
				
			} 	while (cursor1.moveToNext());
		}
		String selectQuery2 = "SELECT  companyName FROM vendor WHERE Id="+vendorId+";";

		
		Cursor cursor2 = db.rawQuery(selectQuery2, null);
		if (cursor2.moveToFirst()) {
			do
		{		vendor=cursor2.getString(0);
				Log.d("vendor",cursor2.getString(0));
				
			} 	while (cursor2.moveToNext());
		}
	msg="Hello there is bid on your crop "+cropname+"("+cropQuality+")"+" with "+
	bidCost+"(Rs. per Ton)"+" for "+quantity+"(Ton)."+"Thanking you.For more information go to app.Be in touch.";	
	Log.d("msg",msg);
	return msg;
	
	  
	}
}
